const express = require('express');
const path = require('path');
const multer = require('multer');
const bcrypt = require('bcrypt');// encryption
const jwt = require('jsonwebtoken');//Generate token

// Initialise Express Server
const app = express();
// Set port for server
const port = 3000;

// JSON body parser middleware
app.use(express.json());
// URL-encoded body parser middleware
app.use(express.urlencoded({ extended: false }));
// Serve the `uploads` directory
app.use('/uploads', express.static('uploads'));

/**
 * Here, path.join(__dirname, '../front-end') constructs the path to the front-end directory.
 * 
 * Accessing the Front-End:
 * With this setup, we can access our front-end by visiting http://localhost:3000 in the browser. The index.html file in the front-end directory will be served as the default entry point.
 * 
 * Benefits of This Structure:
 * - Clear Separation: By keeping the front-end and back-end code in separate directories (front-end and back-end), we maintain a clear separation between the two parts of our application.
 * - Same Port: Both the front-end and back-end are served on the same port (3000), allowing for easy communication between the two without dealing with CORS issues.
 * - Flexibility: This structure allows us to organise our front-end code independently of the back-end, without the constraint of placing it inside the Express `public` directory.
 */
app.use(express.static(path.join(__dirname, '../front-end')));

// Import database modules
const { initialiseDatabase, db } = require('./models/db');

// Initialise database
initialiseDatabase()
  .then(() => {
    //Check if the users and students tables are empty
    const checkTables = async () => {
      try {
        const userCount = await new Promise((resolve, reject) => {
          db.get('SELECT COUNT(*) as count FROM users', (err, row) => {
            if (err) reject(err);
            else resolve(row.count);
          });
        });

        const studentCount = await new Promise((resolve, reject) => {
          db.get('SELECT COUNT(*) as count FROM students', (err, row) => {
            if (err) reject(err);
            else resolve(row.count);
          });
        });

        if (userCount === 0 || studentCount === 0) {
          console.log('Check if the users and students tables are empty. If the database is empty, start inserting initial data');
          require('./models/insert_db');
        } else {
          console.log('The database already contains data, skip data insertion');
        }
      } catch (err) {
        console.error('Error checking database:', err);
      }
    };

    checkTables();
  })
  .catch(err => {
    console.error('Failed to initialize database:', err);
  });


/**
 * Define your API Routes here.
 * Note: The `db` object is imported already. You can use it to query the database.
 * ...
 * ...
 * ...
 * ...
 * ...
 */

//Register Route
app.post('/register', async (req, res) => {
  const { username, email, password } = req.body;

  //Input verification
  if (!username || !email || !password) {
    return res.status(400).json({ message: 'All fields are required' });
  }

  //Password Hash
  const hashedPassword = await bcrypt.hash(password, 10);


  //Store user data
  const insertUserSql = `INSERT INTO users (username, email, password) VALUES (?, ?, ?)`;
  db.run(insertUserSql, [username, email, hashedPassword], function(err) {
    if (err) {
      return res.status(500).json({ message: 'Error registering user' });
    }

    //Obtain the ID of the newly inserted user
    const userId = this.lastID;

    //Create corresponding records in the students table and set the default image path
    const defaultPhotoPath = '../images/default_photo.jpg';
    const insertStudentSql = `
      INSERT INTO students (StudentID, Photo, Bio, Interests, Courses, Achievements)
      VALUES (?, ?, NULL, NULL, NULL, NULL)
    `;
    db.run(insertStudentSql, [userId, defaultPhotoPath], function(err) {
      if (err) {
        return res.status(500).json({ message: 'Error creating student record' });
      }
      res.status(201).json({ message: 'User registered successfully' });
    });
  });
});

//Login router
app.post('/login', async (req, res) => {
  const { email, password } = req.body;
  console.log('Received login request:', email, password);

  const lowerCaseEmail = email.toLowerCase();

  const selectUserSql = `SELECT * FROM users WHERE LOWER(email) = ?`;
  db.get(selectUserSql, [lowerCaseEmail], async (err, row) => {
    if (err) {
      console.error('Database query error:', err);
      return res.status(500).json({ message: 'Internal server error' });
    }
    if (!row) {
      console.log('No user found with email:', lowerCaseEmail);
      return res.status(401).json({ message: 'Invalid email or password' });
    }

    //Ensure that the password field exists
    if (!row.Password) {
      console.log('Password not found for email:', lowerCaseEmail);
      return res.status(401).json({ message: 'Invalid email or password' });
    }

    const isPasswordValid = await bcrypt.compare(password, row.Password);
    if (!isPasswordValid) {
      console.log('Invalid password for email:', lowerCaseEmail);
      return res.status(401).json({ message: 'Invalid email or password' });
    }

    const token = jwt.sign({ userId: row.ID }, 'your_jwt_secret', { expiresIn: '1h' });
    console.log(`Generated token for user ID ${row.ID}: ${token}`);
    res.json({ message: 'Login successful', token, userId: row.ID });
  });
});

//JWT middleware
function authenticateToken(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];
  console.log('Authorization header:', authHeader); // print Authorization
  console.log('Extracted token:', token); // print token

  if (!token) {
      console.error('No token provided');
      return res.status(401).json({ message: 'No token provided' });
  }

  jwt.verify(token, 'your_jwt_secret', (err, user) => {
      if (err) {
          console.error('Token verification failed:', err);
          return res.status(403).json({ message: 'Token verification failed' });
      }
      console.log('Token verified successfully:', user);
      req.user = user;
      next();
  });
}

//Protected Routing
app.get('/protected', authenticateToken, (req, res) => {
  res.json({ message: 'This is a protected route' });
});

//Login registration page routing
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, '../front-end/home.html'));
});

//Student page routing
app.get('/student', (req, res) => {
  res.sendFile(path.join(__dirname, '../front-end/student.html'));
});

//Group page routing
app.get('/group', (req, res) => {
  res.sendFile(path.join(__dirname, '../front-end/group.html'));
});

//Display all profiles
app.get('/profiles', (req, res) => {
  const sql = `
    SELECT users.Username, users.ID, users.Email, students.Photo, students.Bio, students.Interests, students.Courses, students.Achievements
    FROM users
    JOIN students ON users.ID = students.StudentID
  `;
  db.all(sql, [], (err, rows) => {
    if (err) {
      console.error('Error fetching students:', err);
      res.status(500).json({ message: 'Internal server error' });
      return;
    }
    res.json(rows);
  });
});

// Configure Multer
const upload = multer({
  storage: multer.diskStorage({
    destination: 'uploads/',
    filename: (req, file, cb) => {
      cb(null, Date.now() + '-' + file.originalname);
    }
  }),
  fileFilter: (req, file, cb) => {
    if (!['image/jpeg', 'image/png', 'image/gif'].includes(file.mimetype)) {
      return cb(new Error('Only images are allowed'));
    }
    cb(null, true);
  }
});

//Update student profile
app.put('/profiles/:id', upload.single('photo'), authenticateToken, (req, res) => {
  const { id } = req.params;
  const { interests, courses, achievements, bio } = req.body;
  const photo = req.file;

  if (!photo || !interests || !courses || !achievements || !bio) {
      return res.status(400).json({ message: 'All fields are required' });
  }

  //Verify if the user ID in the request matches the ID of the currently logged in user
  if (parseInt(id) !== parseInt(req.user.userId)) {
      return res.status(403).json({ message: 'You are not authorized to update this profile' });
  }

  const photoPath = photo.path;

  const updateProfileSql = `
      UPDATE students
      SET Photo = ?, Interests = ?, Courses = ?, Achievements = ?, Bio = ?
      WHERE StudentID = ?
  `;

  db.run(updateProfileSql, [photoPath, interests, courses, achievements, bio, id], function(err) {
      if (err) {
          console.error('Error updating profile:', err);
          return res.status(500).json({ message: 'Error updating profile' });
      }
      res.status(200).json({ message: 'Profile updated successfully' });
  });
});

//Search for student information
app.get('/studentSearch', (req, res) => {
  const keyword = req.query.keyword.toLowerCase();
  const sql = `
      SELECT users.Username, users.ID, users.Email, students.Photo, students.Bio, students.Interests, students.Courses, students.Achievements
      FROM users
      JOIN students ON users.ID = students.StudentID
      WHERE LOWER(users.ID) LIKE ? OR LOWER(users.Username) LIKE ? OR LOWER(users.Email) LIKE ?
      OR LOWER(students.Interests) LIKE ? OR LOWER(students.Courses) LIKE ? OR LOWER(students.Achievements) LIKE ? OR LOWER(students.Bio) LIKE ?
  `;
  const params = [`%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`];

  db.all(sql, params, (err, rows) => {
      if (err) {
          console.error('Error searching students:', err);
          res.status(500).json({ message: 'Internal server error' });
          return;
      }
      res.json(rows);
  });
});

//Display all group
app.get('/groupList', (req, res) => {
  const sql = `
    SELECT * FROM groups
  `;
  db.all(sql, [], (err, rows) => {
    if (err) {
      console.error('Error fetching groups:', err);
      res.status(500).json({ message: 'Internal server error' });
      return;
    }
    res.json(rows);
  });
});

// Search groups information
app.get('/groupSearch', (req, res) => {
  const keyword = req.query.keyword.toLowerCase();
  const sql = `
      SELECT *
      FROM groups
      WHERE LOWER(GroupID) LIKE ? OR LOWER(GroupName) LIKE ? OR LOWER(Description) LIKE ?
      OR LOWER(Type) LIKE ? OR LOWER(AdminID) LIKE ? OR LOWER(Privacy) LIKE ?
  `;
  const params = [`%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`];

  db.all(sql, params, (err, rows) => {
      if (err) {
          console.error('Error searching groups:', err);
          res.status(500).json({ message: 'Internal server error' });
          return;
      }
      res.json(rows);
  });
});

//Group Type Filter Routing
app.get('/groupFilter', (req, res) => {
  const typesString = req.query.types;

  const types = typesString.split(',');

  let sql = `
      SELECT *
      FROM groups
      WHERE Type IN (${types.map(() => '?').join(', ')})
  `;
  db.all(sql, types, (err, rows) => {
      if (err) {
          console.error('Error filtering groups:', err);
          res.status(500).json({ message: 'Internal server error' });
          return;
      }
      res.json(rows);
  });
});

// Join group
app.post('/joinGroup/:id', authenticateToken, (req, res) => {
  const groupId = req.params.id;
  const userId = req.user.userId;

  //Check if the user has already joined the group
  const checkMembershipSql = `SELECT * FROM members WHERE UserID = ? AND GroupID = ?`;
  db.get(checkMembershipSql, [userId, groupId], (err, row) => {
    if (err) {
      console.error('Error checking membership:', err);
      return res.status(500).json({ success: false, message: 'Internal server error' });
    }

    if (row) {
    //Check if the user has already joined the group
      return res.status(400).json({ success: false, message: 'You are already a member of this group.' });
    }

    //The user has already joined the group but has not yet joined. Allow to join
    const insertMemberSql = `INSERT INTO members (UserID, GroupID) VALUES (?, ?)`;
    db.run(insertMemberSql, [userId, groupId], function(err) {
      if (err) {
        console.error('Error joining group:', err);
        return res.status(500).json({ success: false, message: 'Internal server error' });
      }
      res.json({ success: true, message: 'Joined group successfully' });
    });
  });
});

// Leave group
app.post('/leaveGroup/:id', authenticateToken, (req, res) => {
  const groupId = req.params.id;
  const userId = req.user.userId;

  const deleteMemberSql = `DELETE FROM members WHERE UserID = ? AND GroupID = ?`;
  db.run(deleteMemberSql, [userId, groupId], function(err) {
    if (err) {
      console.error('Error leaving group:', err);
      return res.status(500).json({ success: false, message: 'Internal server error' });
    }
    res.json({ success: true, message: 'Left group successfully' });
  });
});

// Check if user is a member of a group
app.get('/isMember', authenticateToken, (req, res) => {
  const { groupId, userId } = req.query;

  const selectMemberSql = `SELECT * FROM members WHERE UserID = ? AND GroupID = ?`;
  db.get(selectMemberSql, [userId, groupId], (err, row) => {
      if (err) {
          console.error('Error checking membership:', err);
          return res.status(500).json({ success: false, message: 'Internal server error' });
      }
      console.log(`Membership check result for user ${userId} in group ${groupId}:`, row !== undefined);
      res.json({ isMember: row !== undefined });
  });
});

// Delete Group
app.delete('/deleteGroup/:id', authenticateToken, (req, res) => {
  const groupId = req.params.id;
  const userId = req.user.userId;
  console.log(`User ID from token: ${userId}, Group ID from request: ${groupId}`); // 已存在的日志

  //Firstly, check if the user is the administrator of the group
  const selectGroupSql = `SELECT AdminID FROM groups WHERE GroupID = ?`;
  db.get(selectGroupSql, [groupId], (err, row) => {
      if (err) {
          console.error('Error fetching group:', err);
          return res.status(500).json({ success: false, message: 'Internal server error' });
      }

      //Print the value of AdminID
      console.log(`Admin ID for group ${groupId}: ${row ? row.AdminID : 'Not found'}`);

      if (!row || parseInt(row.AdminID) !== parseInt(userId)) {
        return res.status(403).json({ success: false, message: 'You are not authorized to delete this group.' });
    }

      //The user is an administrator and is allowed to delete groups
      const deleteGroupSql = `DELETE FROM groups WHERE GroupID = ?`;
      db.run(deleteGroupSql, [groupId], function(err) {
          if (err) {
              console.error('Error deleting group:', err);
              return res.status(500).json({ success: false, message: 'Internal server error' });
          }

          //Simultaneously delete all member records of the group
          const deleteMembersSql = `DELETE FROM members WHERE GroupID = ?`;
          db.run(deleteMembersSql, [groupId], function(err) {
              if (err) {
                  console.error('Error deleting members:', err);
                  return res.status(500).json({ success: false, message: 'Internal server error' });
              }

              res.json({ success: true, message: 'Group deleted successfully' });
          });
      });
  });
});

//Get detailed group information
app.get('/getGroupDetails/:id', (req, res) => {
  const groupId = req.params.id;
  const sql = `SELECT * FROM groups WHERE GroupID = ?`;
  db.get(sql, [groupId], (err, row) => {
    if (err) {
      console.error('Error fetching group details:', err);
      return res.status(500).json({ message: 'Internal server error' });
    }
    if (!row) {
      return res.status(404).json({ message: 'Group not found' });
    }
    res.json(row);
  });
});

//Update group information
app.put('/updateGroup/:id', authenticateToken, (req, res) => {
  const groupId = req.params.id;
  const userId = req.user.userId;
  const { GroupName, Description, Type, Privacy } = req.body;

  //Firstly, check if the user is the administrator of the group
  const selectGroupSql = `SELECT AdminID FROM groups WHERE GroupID = ?`;
  db.get(selectGroupSql, [groupId], (err, row) => {
      if (err) {
          console.error('Error fetching group:', err);
          return res.status(500).json({ success: false, message: 'Internal server error' });
      }

      if (!row || parseInt(row.AdminID) !== parseInt(userId)) {
          return res.status(403).json({ success: false, message: 'You are not authorized to update this group.' });
      }

      //The user is an administrator and is allowed to update the group
      const updateGroupSql = `
          UPDATE groups
          SET GroupName = ?, Description = ?, Type = ?, Privacy = ?
          WHERE GroupID = ?
      `;
      db.run(updateGroupSql, [GroupName, Description, Type, Privacy, groupId], function(err) {
          if (err) {
              console.error('Error updating group:', err);
              return res.status(500).json({ success: false, message: 'Internal server error' });
          }
          res.json({ success: true, message: 'Group updated successfully' });
      });
  });
});

//Create Group
app.post('/createGroup', authenticateToken, (req, res) => {
  const { GroupName, Description, Type, Privacy, AdminID } = req.body;

  //input validation
  if (!GroupName || !Description || !Type || !Privacy || !AdminID) {
      return res.status(400).json({ success: false, message: 'All fields are required' });
  }

  console.log('Creating group:', { GroupName, Description, Type, Privacy, AdminID });

  //Insert new group record
  const insertGroupSql = `
      INSERT INTO groups (GroupName, Description, Type, Privacy, AdminID)
      VALUES (?, ?, ?, ?, ?)
  `;
  db.run(insertGroupSql, [GroupName, Description, Type, Privacy, AdminID], function(err) {
      if (err) {
          console.error('Error creating group:', err);
          return res.status(500).json({ success: false, message: 'Internal server error' });
      }
      console.log('Group created successfully');
      res.json({ success: true, message: 'Group created successfully' });
  });
});

// Start the Express server
app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});

// Export Express app
module.exports = app;
